﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using AliboboStockLib.date;
using AliboboStockLib.linq;

namespace AliboboStockLib.stockupdater.sbdetail
{
    public class TseDailyIndexUpdater
    {

        public static int getCountOnDate(DateTime d)
        {
            String sql=String.Format("select COUNT(*) from StockDaily where StockDaily.日期 ='{0}' " +
                                      "and StockDaily.證券代號 in (select 代號 from StockIndex where StockIndex.市場='tse')",DateUtils.getSlashYYYYMMDD(d));
            MyStockDataContext context = LinqAgent.getContext();
            int  c = context.ExecuteQuery<int>(sql).FirstOrDefault<int>();
            return c;
        }
        protected static int getIndexDataCount(String market, DateTime d)
        {

            String sqltemp = "Select COUNT(*) from StockDaily where StockDaily.日期='{0}' and StockDaily.證券代號 in (select StockIndex.代號 from stockindex where StockIndex.市場='{1}')";
            String sql=String.Format(sqltemp, DateUtils.getSlashYYYYMMDD(d), market);
            MyStockDataContext context=getContext();
            int r=context.ExecuteQuery<int>(sql).FirstOrDefault<int>();
            return r;

        }

        private static MyStockDataContext getContext()
        {
            MyStockDataContext context = LinqAgent.getContext();
            return context;
        }
        protected static void deleteDataOnDate(DateTime d)
        {
            String sql = "delete from StockDaily where StockDaily.日期 ='" + DateUtils.getSlashYYYYMMDD(d) +
                "'  and StockDaily.證券代號 in (select 代號 from StockIndex where StockIndex.市場='tse') ";
            MyStockDataContext context = LinqAgent.getContext();
            int c=context.ExecuteCommand(sql);
            
        }

        public static void updateStockDaily(DateTime d, List<List<String>> datas, String market, IProgressMonitor progressMonitor)
        {
            int c = getIndexDataCount("tse", d);
            if (c == datas.Count)
                return;
            deleteDataOnDate(d);
            //先砍掉... -->怎樣join. ?
            StockCodeManager stockCodeManager = new StockCodeManager();
            //先處理代號
            foreach (List<String> data in datas)
            {
                Guid id = Guid.Empty;
                String code = data[0];
                if (StockCodeManager.getStockCode(code, ref id) == false)
                    StockCodeManager.createTSEStockCode(data[0], data[1]);
            }


            String sql = "insert into StockDaily ([日期],[證券代號],[成交股數],[成交筆數],[成交金額]," +
                                                  "[開盤價], [最高價],[最低價], [收盤價], [漲跌], [漲跌價差]," +
                                                  "[最後揭示買價],[最後揭示買量], [最後揭示賣價], [最後揭示賣量], [本益比]) " +
                                                  "values (@日期,@證券代號,@成交股數,@成交筆數,@成交金額, @開盤價, @最高價,@最低價, @收盤價, @漲跌, @漲跌價差, " +
                                                  "@最後揭示買價,@最後揭示買量, @最後揭示賣價, @最後揭示賣量, @本益比)";
            DbTransaction transaction = SQLFactory.getInstance(0).BeginTransation();
            DbCommand cmd = SQLFactory.getInstance(0).getCommand();
            cmd.Transaction = transaction;
            cmd.CommandText = sql;



            DbParameter 日期 = SQLFactory.getInstance(0).createParam("@日期", DbType.Date, null);
            DbParameter 證券代號 = SQLFactory.getInstance(0).createParam("@證券代號", DbType.String, null);
            DbParameter 成交股數 = SQLFactory.getInstance(0).createParam("@成交股數", DbType.Int32, null);
            DbParameter 成交筆數 = SQLFactory.getInstance(0).createParam("@成交筆數", DbType.Int32, null);
            DbParameter 成交金額 = SQLFactory.getInstance(0).createParam("@成交金額", DbType.Decimal, null);
            DbParameter 開盤價 = SQLFactory.getInstance(0).createParam("@開盤價",null);
            DbParameter 最高價 = SQLFactory.getInstance(0).createParam("@最高價", DbType.Decimal, null);
            DbParameter 最低價 = SQLFactory.getInstance(0).createParam("@最低價", DbType.Decimal, null);
            DbParameter 收盤價 = SQLFactory.getInstance(0).createParam("@收盤價", DbType.Decimal, null);
            DbParameter 漲跌 = SQLFactory.getInstance(0).createParam("@漲跌", DbType.String, null);
            DbParameter 漲跌價差 = SQLFactory.getInstance(0).createParam("@漲跌價差", DbType.Decimal, null);
            DbParameter 最後揭示買價 = SQLFactory.getInstance(0).createParam("@最後揭示買價", DbType.Decimal, null);
            DbParameter 最後揭示買量 = SQLFactory.getInstance(0).createParam("@最後揭示買量", DbType.Int32, null);
            DbParameter 最後揭示賣價 = SQLFactory.getInstance(0).createParam("@最後揭示賣價", DbType.Decimal, null);
            DbParameter 最後揭示賣量 = SQLFactory.getInstance(0).createParam("@最後揭示賣量", DbType.Int32, null);
            DbParameter 本益比 = SQLFactory.getInstance(0).createParam("@本益比", DbType.Decimal, null);
            cmd.Parameters.Add(日期);
            cmd.Parameters.Add(證券代號);
            cmd.Parameters.Add(成交股數);
            cmd.Parameters.Add(成交筆數);
            cmd.Parameters.Add(成交金額);
            cmd.Parameters.Add(開盤價);
            cmd.Parameters.Add(最高價);
            cmd.Parameters.Add(最低價);
            cmd.Parameters.Add(收盤價);
            cmd.Parameters.Add(漲跌);
            cmd.Parameters.Add(漲跌價差);
            cmd.Parameters.Add(最後揭示買價);
            cmd.Parameters.Add(最後揭示買量);
            cmd.Parameters.Add(最後揭示賣價);
            cmd.Parameters.Add(最後揭示賣量);
            cmd.Parameters.Add(本益比);


            decimal v = new decimal(0);
            日期.Value = d;

            foreach (List<String> data in datas)
            {
                if (data[0].Contains("證券"))
                    continue;

                證券代號.Value = data[0];

                if (decimal.TryParse(data[2], out v))
                    成交股數.Value = Convert.ToInt32(v);
                else
                    成交股數.Value = 0;

                if (decimal.TryParse(data[3], out v))
                {
                    成交筆數.Value = Convert.ToInt32(v);
                }
                else
                {
                    成交筆數.Value = 0;
                }

                if (decimal.TryParse(data[4], out v))
                {
                    成交金額.Value = v;
                }
                else
                {
                    成交金額.Value = 0;
                }
                if (decimal.TryParse(data[5], out v))
                    開盤價.Value = v;
                else
                {
                    開盤價.Value = DBNull.Value;
                    
                }

                if (decimal.TryParse(data[6], out v))
                    最高價.Value = v;
                else
                    最高價.Value = DBNull.Value; ;

                if (decimal.TryParse(data[7], out v))
                    最低價.Value = v;
                else
                    最低價.Value = DBNull.Value; ;

                if (decimal.TryParse(data[8], out v))
                    收盤價.Value = v;
                else
                    收盤價.Value = DBNull.Value; ;

                bool positive = true;
                {
                    if (data[9].Contains("＋"))
                        漲跌.Value = "漲";
                    else
                        if (data[9].Contains("－"))
                        {
                            漲跌.Value = "跌";
                            positive = false;
                        }
                        else
                            if (data[9].Contains("X"))
                                漲跌.Value = "不比價";
                            else
                                漲跌.Value = "";
                }

                //漲跌價差[10]
                if (decimal.TryParse(data[10], out v))
                {
                    if (positive == false)
                        v = (new decimal(0)) - v;
                    漲跌價差.Value = v;
                }
                else
                {
                    漲跌價差.Value = DBNull.Value; ;
                }

                if (decimal.TryParse(data[11], out v))
                {
                    最後揭示買價.Value = v;
                }
                else
                {
                    最後揭示買價.Value = DBNull.Value; ;
                }
                if (decimal.TryParse(data[12], out v))
                {
                    最後揭示買量.Value = Convert.ToInt32(v);
                }
                else
                {
                    最後揭示買量.Value = DBNull.Value; ;
                }
                //最後揭示賣價[13]
                if (decimal.TryParse(data[13], out v))
                {
                    最後揭示賣價.Value = v;
                }
                else
                {
                    最後揭示賣價.Value = DBNull.Value; ;
                }

                //最後揭示賣量[14]
                if (decimal.TryParse(data[14], out v))
                {
                    最後揭示賣量.Value = Convert.ToInt32(v);
                }
                else
                {
                    最後揭示賣量.Value = DBNull.Value; ;
                }
                if (decimal.TryParse(data[15], out v))
                {
                    本益比.Value = v;
                }
                else
                {
                    本益比.Value = DBNull.Value; ;
                }
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ee)
                {
                    throw ee;
                }

                
            }

            transaction.Commit();
            cmd.Dispose();
            transaction.Dispose();

        }
        /// <summary>
        /// 取得有效的最大交易日期
        /// </summary>
        /// <returns></returns>
        public static  DateTime getMaxTradeDate()
        {
            DateTime now = DateTime.Now;
            MyStockDataContext context = LinqAgent.getContext();
            try
            {
                DateTime max = (from r in context.StockDailies select r.日期).Max<DateTime>();
                if (max.CompareTo(now) > 0)
                    throw new Exception("資料庫內容有誤,最大交易日期比現在大");
                return max;
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine(e.Message);
                return DateUtils.getValidTradeDate(DateTime.Now);
            }
        }


    }
}
